<?php
/**
 * Created by muniao.
 * User: muniao
 * Date: 2018/7/31
 * Time: 16:00
 */

namespace app\api\controller\android\v7;


use think\Cache;
use think\Controller;
use app\api\model\Authority;
use think\db;

use think\Request;
use  app\api\model\OrderModel;

class Financial extends Controller
{
    protected $model;
    protected $us;
    
    public function _initialize()
    {
        $this->model=new Authority();
        $this->us   =$this->model->check(1);
    }
    
    public function state()
    {
        $data=[
            [
                'type' =>1,
                'title'=>'签约时间',
            ],
            [
                'type' =>2,
                'title'=>'完工时间',
            ],
            [
                'type' =>3,
                'title'=>'完款时间',
            ],
            [
                'type' =>4,
                'title'=>'结算时间',
            ],
        ];
        r_date($data, 200);
    }
    
    public function user()
    {
        $user=\db('user')->where('store_id', $this->us['store_id'])->where('status', 0)->field('user_id,username')->select();
        r_date($user, 200);
    }
    
    /*
     * 全部店长
     */
    public function whole()
    {
        $data =\request()->get();
        $users=\db('user');
        if (isset($data['user_name']) && $data['user_name'] != '') {
            $users->whereLike('user.username', "%{$data['user_name']}%");
        }
        if (isset($data['store_id']) && $data['store_id'] != 0) {
            $users->whereLike('user.store_id', $data['store_id']);
        }
        $user=$users->where('user.status', 0)->join('store', 'store.store_id=user.store_id', 'left')->field('user.user_id,user.username,user.mobile,user.work_tag,store.store_name,user.store_id')->select();
        foreach ($user as $k=>$v) {
            $work_tag=empty($v['work_tag'])? [0, 0, 0, 0] : explode(',', $v['work_tag']);
            
            $user[$k]['work_tags']=[];
            foreach ($work_tag as $ks=>$value) {
                switch ($ks) {
                    case 0:
                        $values='建筑渗漏水维修专家';
                        break;
                    case 1:
                        $values='旧房改造类专家';
                        break;
                    case 2:
                        $values='墙面维修专家';
                        break;
                    case 3:
                        $values='室内渗漏水维修专家';
                        break;
                }
                
                if ($value == 1) {
                    $user[$k]['work_tags'][]=$values;
                }
            }
        }
        r_date($user, 200);
    }
    
    /*
     * 店铺成交量
     * 签约时间，完工时间，完款时间，结算时间
     */
    public function ShopClose(OrderModel $orderModel)
    {
        $params=\request()->get();
        $start =strtotime(date('Y-m-01 00:00:00', strtotime($params['startTime'])));//获取指定月份的第一天
        $end   =strtotime(date('Y-m-t 23:59:59', strtotime($params['startTime']))); //获取指定月份的最后一天
        
        $m=$orderModel->table('order')
            ->alias('a')
            ->field('concat(p.province,c.city,u.county,a.addres) as  addres,a.contacts,co.con_time,a.order_id,a.state,go.title,a.assignor')
            ->join('province p', 'a.province_id=p.province_id', 'left')
            ->join('city c', 'a.city_id=c.city_id', 'left')
            ->join('county u', 'a.county_id=u.county_id', 'left')
            ->join('user us', 'a.assignor=us.user_id', 'left')
            ->join('goods_category go', 'a.pro_id=go.id', 'left')
            ->join('contract co', 'a.contract_id=co.contract_id', 'left');
        if (isset($params['user_id']) && $params['user_id'] != 0) {
            $m->where('a.assignor', $params['user_id']);
        }
        switch ($params['type']) {
            //签约时间
            case 1:
                $m->whereBetween('co.con_time', [$start, $end]);
                $m->order('co.con_time desc');
                break;
            //完工时间
            case 2:
                $m->whereBetween('a.finish_time', [$start, $end]);
                $m->order('a.finish_time desc');
                break;
            //完款时间
            case 3:
                $m->whereBetween('a.received_time', [$start, $end]);
                $m->order('a.received_time desc');
                break;
            //结算时间
            default :
                $m->whereBetween('a.cleared_time', [$start, $end]);
                $m->order('a.cleared_time desc');
                break;
        }
        $list=$m->where('us.store_id', $this->us['store_id'])->where('us.ce', '<>', 2)->where('a.state', '<>', 9)->select();
        
        foreach ($list as $k=>$key) {
            //签约时间
            $list[$k]['con_time']=!empty($key['con_time'])? date('Y-m-d H:i:s', $key['con_time']) : '';
            $mouth               =$orderModel->offer($key['order_id']);
            //签约金额
            $list[$k]['amount']=sprintf('%.2f', $mouth['amount']);
            //代购主材
            $list[$k]['agency']=sprintf('%.2f', $mouth['agency']);
        }
        if (isset($params['Test']) && $params['Test'] == 1) {
            res_date($list, 200);
        } else{
            r_date($list, 200);
        }
    }
    
    /*
     * 店铺财务
     * 预算/结算
     */
    public function ShopFinance(OrderModel $orderModel)
    {
        
        $params=\request()->get();
        $start =strtotime(date('Y-m-01 00:00:00', strtotime($params['startTime'])));//获取指定月份的第一天
        $end   =strtotime(date('Y-m-t 23:59:59', strtotime($params['startTime']))); //获取指定月份的最后一天
        
        $m=$orderModel->table('order')
            ->alias('a')
            ->field('concat(p.province,c.city,u.county,a.addres) as  addres,a.contacts,co.con_time,a.order_id,a.state,a.assignor,go.title')
            ->join('province p', 'a.province_id=p.province_id', 'left')
            ->join('city c', 'a.city_id=c.city_id', 'left')
            ->join('county u', 'a.county_id=u.county_id', 'left')
            ->join('user us', 'a.assignor=us.user_id', 'left')
            ->join('goods_category go', 'a.pro_id=go.id', 'left')
            ->join('contract co', 'a.contract_id=co.contract_id', 'left');
        if (isset($params['user_id']) && $params['user_id'] != 0) {
            $m->where('a.assignor', $params['user_id']);
        }
        switch ($params['type']) {
            //签约时间
            case 1:
                $m->whereBetween('co.con_time', [$start, $end]);
                $m->order('co.con_time desc');
                break;
            //完工时间
            case 2:
                $m->whereBetween('a.finish_time', [$start, $end]);
                $m->order('a.finish_time desc');
                break;
            //完款时间
            case 3:
                $m->whereBetween('a.received_time', [$start, $end]);
                $m->order('a.received_time desc');
                break;
            //结算时间
            default :
                $m->whereBetween('a.cleared_time', [$start, $end]);
                $m->order('a.cleared_time desc');
                break;
        }
        $list=$m->where('us.store_id', $this->us['store_id'])->where('us.ce', '<>', 2)->where('a.state', '<>', 9)->select();
        foreach ($list as $k=>$key) {
            $data                =[];
            $list[$k]['con_time']=!empty($key['con_time'])? date('Y-m-d H:i:s', $key['con_time']) : '';
            $mouth               =$orderModel->offer($key['order_id']);
            //签约金额
            $list[$k]['amount']=sprintf('%.2f', $mouth['amount']);
            //代购主材
            $list[$k]['agency']=sprintf('%.2f', $mouth['agency']);
            $da['ification']   =2;
            $da['order_id']    =$key['order_id'];
            $data[]            =$da;
            //报销金额
            $list[$k]['agency_sum']=sprintf('%.2f', db('reimbursement')->where(['order_id'=>$key['order_id'], 'status'=>1, 'classification'=>['<>', 4]])->sum('money'));
            if (isset($params['expected'])) {
                //人工预算
                $list[$k]['estimate']=sprintf('%.2f', db('capital')->where(['ordesr_id'=>$key['order_id'], 'types'=>1, 'enable'=>1, ' agency'=>0])->sum('labor_cost'));
                //材料预算
                $list[$k]['MaterialBudget']=sprintf('%.2f', Financial::budget($key['order_id']));
            } else{
                $capital=$cap=db('capital')->where(['ordesr_id'=>$key['order_id'], 'types'=>1, 'enable'=>1])->field('class_b,capital_id')->column('capital_id');
                if (!empty($capital)) {
                    $sql="SELECT SUM(`personal_price`) AS sum FROM app_user_order_capital AS a, (SELECT b.`user_id`, b.`order_id`,b.`capital_id`,MAX(b.created_at) AS `created_at` FROM app_user_order_capital AS b GROUP BY b.`user_id`,b.`order_id`,b.`capital_id`)AS c WHERE a.`user_id`=c.`user_id` AND a.`order_id`=c.`order_id` AND a.`capital_id`=c.`capital_id` AND a.created_at = c.created_at And a.`deleted_at` IS NULL AND a.`capital_id` in(" . implode(',', $capital) . ")";
                    //原生sql
                    $reality_artificial=Db::connect(config('database.db2'))->table('app_user_order_capital');
                    $sum               =$reality_artificial->query($sql);
                    if (isset($sum[0]['sum'])) {
                        $order_for_reality_artificial=round($sum[0]['sum'], 2);
                    } else{
                        $order_for_reality_artificial=0;
                    }
                } else{
                    $order_for_reality_artificial=0;
                }
                //人工实际
                $list[$k]['estimate']=sprintf('%.2f', $order_for_reality_artificial);
                //材料实际
                $list[$k]['MaterialBudget']=sprintf('%.2f', db('material_usage')
                    ->whereIn('order_id', $key['order_id'])
                    ->where('status', 1)
                    ->sum('total_price'));
            }
            //利润
            $list[$k]['profit']=sprintf('%.2f', $list[$k]['amount'] - $list[$k]['estimate'] - $list[$k]['MaterialBudget'] - $list[$k]['agency'] - $list[$k]['agency_sum']);
            
        }
        if (isset($params['Test']) && $params['Test'] == 1) {
            res_date($list, 200);
        } else{
            r_date($list, 200);
        }
        
        
    }
    
    
    /*
     * 店铺费用查看
     */
    public function shopExpenses($inside=1, $startTimes=0)
    {
        
        $params=\request()->get();
        if (isset($params['startTime'])) {
            $startTime=$params['startTime'];
        } else{
            $startTime=$startTimes;
        }
        
        $start =strtotime(date('Y-m-01 00:00:00', strtotime($startTime)));//获取指定月份的第一天
        $end   =strtotime(date('Y-m-t 23:59:59', strtotime($startTime))); //获取指定月份的最后一天
        $subsql=db('store_cost')
            ->where('store_id', $this->us['store_id'])
            ->where('created_time', '<=', $end)
            ->field('store_id,cost_type,max(created_time) as created_time')
            ->group('store_id,cost_type')
            ->buildSql();
        
        $data=db('store_cost')->alias('a')
            ->join([$subsql=>'b'], 'a.store_id=b.store_id and a.cost_type=b.cost_type and a.created_time=b.created_time')
            ->order('a.store_id,a.cost_type')
            ->field('a.cost_type,a.money')
            ->order('a.cost_type ASC')
            ->select();
        
        $date=[
            [
                'cost_type'=>14,
                'money'    =>'0.00',
            ],
            [
                'cost_type'=>15,
                
                'money'=>'0.00',
            ],
            [
                'cost_type'=>16,
                
                'money'=>'0.00',
            ],
        
        ];
        $d   =array_merge($data, $date);
        foreach ($d as $j=>$datum) {
            switch ($datum['cost_type']) {
                case 1:
                    $data[$j]['cost_name']='办公费';
                    break;
                case 2:
                    $data[$j]['cost_name']='基本工资';
                    break;
                case 3:
                    $data[$j]['cost_name']='运输费';
                    break;
                case 4:
                    $data[$j]['cost_name']='折旧费';
                    break;
                case 5:
                    $data[$j]['cost_name']='房租';
                    break;
                case 6:
                    $data[$j]['cost_name']='福利费';
                    break;
                case 7:
                    $data[$j]['cost_name']='水电物管费';
                    break;
                case 8:
                    $data[$j]['cost_name']='招待费';
                    break;
                case 9:
                    $data[$j]['cost_name']='维修费';
                    break;
                case 10:
                    $data[$j]['cost_name']='脚手架费用';
                    break;
                case 11:
                    $data[$j]['cost_name']='建渣清运费';
                    break;
                case 12:
                    $data[$j]['cost_name']='企业微信报销';
                    break;
                case 13:
                    $data[$j]['cost_name']='库存盘点差异';
                    break;
                case 14:
                    $data[$j]['cost_name']='储备店长提成';
                    $op                   =Financial::quan($this->us['store_id'], $start, $end);
                    $data[$j]['money']    =$op['TotalProfit'];
                    break;
                case 15:
                    $where                =[
                        'order.notcost_time'=>[['EXP', Db::raw(' IS NULL ')], ['eq', 0], 'or'],
                    ];
                    $data[$j]['cost_name']='订单费用';
                    $count                =db('order')
                        ->join('user', 'user.user_id=order.assignor', 'left')
                        ->where('user.store_id', $this->us['store_id'])
                        ->where('user.ce', '<>', 2)
                        ->where($where)
                        ->whereBetween('order.created_time', [$start, $end])
                        ->count();
                    $data[$j]['money']    =$count * 120;
                    break;
                default:
                    $data[$j]['cost_name']='师傅人工提成';
                    $order_id             =db('order')
                        ->join('user', 'user.user_id=order.assignor', 'left')
                        ->whereBetween('order.cleared_time', [$start, $end])
                        ->where('user.store_id', $this->us['store_id'])
                        ->where('user.ce', '<>', 2)
                        ->column('order_id');
                    $capital              =db('capital')->whereIn('ordesr_id', $order_id)->where(['types'=>1, 'enable'=>1, 'agency'=>0])->column('capital_id');
                    if (!empty($capital)) {
                        $sql="SELECT SUM(`personal_price`) AS sum FROM app_user_order_capital AS a, (SELECT b.`user_id`, b.`order_id`,b.`capital_id`,MAX(b.created_at) AS `created_at` FROM app_user_order_capital AS b GROUP BY b.`user_id`,b.`order_id`,b.`capital_id`)AS c WHERE a.`user_id`=c.`user_id` AND a.`order_id`=c.`order_id` AND a.`capital_id`=c.`capital_id` AND a.created_at = c.created_at And a.`deleted_at` IS NULL AND a.`capital_id` in(" . implode(',', $capital) . ")";
                        //原生sql
                        $reality_artificial=Db::connect(config('database.db2'))->table('app_user_order_capital');
                        $sum               =$reality_artificial->query($sql);
                        if (isset($sum[0]['sum'])) {
                            $order_for_reality_artificial=sprintf('%.2f ', $sum[0]['sum']);
                        } else{
                            $order_for_reality_artificial=0;
                        }
                    } else{
                        $order_for_reality_artificial=0;
                    }
                    $data[$j]['money']=$order_for_reality_artificial;
                    break;
            }
            
        }
        if ($inside == 1) {
            r_date(['data'=>$data, 'count'=>sprintf('%.2f ', array_sum(array_column($data, 'money')))], 200);
        } else{
            return array_sum(array_column($data, 'money'));
        }
        
    }
    
    /**
     * 提成
     */
    
    public static function Commission($store_id=0, $user_id=0, $start, $end)
    {
        $quer       =\db('order')
            ->join('user', 'order.assignor=user.user_id', 'left');
        $quer_agency=\db('order')
            ->join('user', 'order.assignor=user.user_id', 'left');
        if ($store_id != 0) {
            $quer->where('user.store_id', $store_id);
            $quer_agency->where('user.store_id', $store_id);
            
        } elseif ($user_id != 0){
            $quer->where('user.user_id', $user_id);
            $quer_agency->where('user.user_id', $user_id);
        }
        $user=$quer->where('user.reserve', 2)->where('user.ce', '<>', 2)->whereBetween('order.cleared_time', [$start, $end])->whereNotNull('order.received_time')->where('order.state', 7)->where('user.status', 0)->distinct(true)->column('order.order_id');
    
        $quer_a=$quer_agency->where('user.reserve', 2)->where('user.ce', '<>', 2)->whereBetween('order.settlement_time', [$start, $end])->distinct(true)->column('order.order_id');
        //代购主材金额
        $agency_capital     =db('capital')->whereIn('ordesr_id', $quer_a)->where(['types'=>1, 'enable'=>1, 'agency'=>1])->sum('to_price');
        $through            =db('capital')->whereIn('ordesr_id', $quer_a)->where(['types'=>1, 'enable'=>1, 'agency'=>1])->column('ordesr_id');
        $thro               =db('through')->whereIn('order_ids', $through)->where('baocun', 1)->field('through_id,order_ids')->select();
        $s1                 =array_column($thro, 'order_ids');
        $arr3               =array_diff($quer_a, $s1);
        $purchasing_discount=0;
        $purchasing_expense =0;
      
        if ($thro) {
        
            $ca=db('envelopes')->whereIn('through_id', array_column($thro, 'through_id'))->field('sum(purchasing_discount) as purchasing_discount,sum(purchasing_expense) as purchasing_expense')->select();
            
            if ($ca) {
                foreach ($ca as $value) {
                    $purchasing_discount+=$value['purchasing_discount'];
                    $purchasing_expense +=$value['purchasing_expense'];
                }
            }
            
            
        }
       
        if ($arr3) {
            $ca1=db('envelopes')->whereIn('ordesr_id', $arr3)->where('through_id', 0)->field('sum(purchasing_discount) as purchasing_discount,sum(purchasing_expense) as purchasing_expense')->select();
            if ($ca1) {
                foreach ($ca1 as $value) {
                    $purchasing_discount+=$value['purchasing_discount'];
                    $purchasing_expense +=$value['purchasing_expense'];
                }
            }
            
            
        }
        
        $agency=$agency_capital + $purchasing_expense - $purchasing_discount;//代购主材;
        //代购报销
        $agency_sum=db('reimbursement')->whereIn('order_id', $quer_a)->where(['status'=>1, 'classification'=>4])->sum('money');
        $data      =[];
        foreach ($user as $key=>$item) {
            $amount                =OrderModel::offer($item, 2);
            $data[$key]['order_id']=$item;
            //完工完款金额除去主材
            $data[$key]['amount']=$amount['amount'] - $amount['agency'];
            //人工费
            $capital=db('capital')->whereIn('ordesr_id', $item)->where(['types'=>1, 'enable'=>1, 'agency'=>0])->field('class_b,capital_id')->column('capital_id');
            if (!empty($capital)) {
                $sql="SELECT SUM(`personal_price`) AS sum FROM app_user_order_capital AS a, (SELECT b.`user_id`, b.`order_id`,b.`capital_id`,MAX(b.created_at) AS `created_at` FROM app_user_order_capital AS b GROUP BY b.`user_id`,b.`order_id`,b.`capital_id`)AS c WHERE a.`user_id`=c.`user_id` AND a.`order_id`=c.`order_id` AND a.`capital_id`=c.`capital_id` AND a.created_at = c.created_at And a.`deleted_at` IS NULL AND a.`capital_id` in(" . implode(',', $capital) . ")";
                //原生sql
                $reality_artificial=Db::connect(config('database.db2'))->table('app_user_order_capital');
                $sum               =$reality_artificial->query($sql);
                if (isset($sum[0]['sum'])) {
                    $order_for_reality_artificial=round($sum[0]['sum'], 2);
                } else{
                    $order_for_reality_artificial=0;
                }
            } else{
                $order_for_reality_artificial=0;
            }
            $data[$key]['money']=$order_for_reality_artificial;
            //材料报销
            $data[$key]['material_usage']=sprintf('%.2f', db('material_usage')->where('status', 1)->where(['order_id'=>$item])->sum('total_price'));
            //报销费用
            $data[$key]['reimbursement']=db('reimbursement')->where(['order_id'=>$item, 'status'=>1, 'classification'=>['<>', 4]])->sum('money');
            
        }
        
        
        $money['amounts']       =sprintf('%.2f', array_sum(array_column($data, 'amount')));
        $money['agency']        =sprintf('%.2f', $agency);
        $money['material_usage']=sprintf('%.2f', array_sum(array_column($data, 'material_usage')));
        $money['reimbursement'] =sprintf('%.2f', array_sum(array_column($data, 'reimbursement')));
        $money['agency_sum']    =sprintf('%.2f', $agency_sum);
        $money['money']         =sprintf('%.2f', array_sum(array_column($data, 'money')));
        $count                  =($money['amounts'] + $money['agency'] - $money['material_usage'] - $money['reimbursement'] - $money['agency_sum'] - $money['money']) * 0.02;
        
        return ['count'=>sprintf('%.2f', $count), 'date'=>$data, 'money'=>$money];
        
    }
    
    /*
     * 材料预算
     */
    public static function budget($order_id)
    {
        $material  =0;
        $labor_cost=db('capital')
            ->where('ordesr_id', $order_id)
            ->where('types', 1)
            ->where('enable', 1)
            ->where('agency', 0)
            ->field('projectId,square,material')
            ->select();
        foreach ($labor_cost as $k) {
            $material+=$k['material'];
        }
        return $material;
    }
    
    /*
     * 订单费用
     */
    public function OrderFee()
    {
        $params=\request()->get();
        $start =strtotime(date('Y-m-01 00:00:00', strtotime($params['startTime'])));//获取指定月份的第一天
        $end   =strtotime(date('Y-m-t 23:59:59', strtotime($params['startTime']))); //获取指定月份的最后一天
        //计入费用
        $where      =[
            'order.notcost_time'=>[['EXP', Db::raw(' IS NULL ')], ['eq', 0], 'or'],
        ];
        $IncludediIn=db('order')
            ->join('user', 'user.user_id=order.assignor', 'left')
            ->join('contract co', 'order.contract_id=co.contract_id', 'left')
            ->where('user.store_id', $this->us['store_id'])
            ->where($where)
            ->where('user.ce', '<>', 2)
            ->whereBetween('order.created_time', [$start, $end])
            ->count();
        
        //不计入费用
        $wheres        =[
            'order.notcost_time'=>[['EXP', Db::raw('IS NOT NULL')], ['neq', 0], 'and'],
        ];
        $IncludediNotIn=db('order')
            ->join('user', 'user.user_id=order.assignor', 'left')
            ->join('contract co', 'order.contract_id=co.contract_id', 'left')
            ->where('user.store_id', $this->us['store_id'])
            ->where($wheres)
            ->where('user.ce', '<>', 2)
            ->whereBetween('order.created_time', [$start, $end])
            ->count();
        r_date(['IncludediIn'=>$IncludediIn . '个/￥' . $IncludediIn * 120, 'IncludediNotIn'=>$IncludediNotIn . '个/￥' . $IncludediNotIn * 120, 'count'=>$IncludediIn * 120 + $IncludediNotIn * 120], 200);
    }
    
    /*
     *订单费用列表
     */
    
    public function OrderFeeList(OrderModel $orderModel)
    {
        $params=\request()->get();
        
        $m=$orderModel->table('order')
            ->alias('a')
            ->field('concat(p.province,c.city,u.county,a.addres) as  addres,a.contacts,co.con_time,a.order_id,a.state,go.title,a.assignor')
            ->join('province p', 'a.province_id=p.province_id', 'left')
            ->join('city c', 'a.city_id=c.city_id', 'left')
            ->join('county u', 'a.county_id=u.county_id', 'left')
            ->join('user us', 'a.assignor=us.user_id', 'left')
            ->join('goods_category go', 'a.pro_id=go.id', 'left')
            ->join('contract co', 'a.contract_id=co.contract_id', 'left');
        switch ($params['type']) {
            //计入
            case 1:
                $where=[
                    'a.notcost_time'=>[['EXP', Db::raw(' IS NULL ')], ['eq', 0], 'or'],
                ];
                
                $m->where($where);
                break;
            //不计入
            case 2:
                $wheres=[
                    'a.notcost_time'=>[['EXP', Db::raw('IS NOT NULL')], ['neq', 0], 'and'],
                ];
                $m->where($wheres);
                break;
        }
        if (isset($params['startTime']) && $params['startTime'] != '') {
            $start=strtotime(date('Y-m-01 00:00:00', strtotime($params['startTime'])));//获取指定月份的第一天
            $end  =strtotime(date('Y-m-t 23:59:59', strtotime($params['startTime']))); //获取指定月份的最后一天
            $m->whereBetween('a.created_time', [$start, $end]);
        }
        $list=$list=$m->where('us.store_id', $this->us['store_id'])->where('us.ce', '<>', 2)->select();
        r_date(['count'=>count($list), 'money'=>count($list) * 120, 'data'=>$list], 200);
        
    }
    
    /*
     * 师傅人工提成
     */
    public function ArtificialCommission(OrderModel $orderModel)
    {
        $params=\request()->get();
        $start =strtotime(date('Y-m-01 00:00:00', strtotime($params['startTime'])));//获取指定月份的第一天
        $end   =strtotime(date('Y-m-t 23:59:59', strtotime($params['startTime']))); //获取指定月份的最后一天
        $limit =isset($params['limit'])? $params['limit'] : 20;
        $m     =$orderModel->table('order')
            ->alias('a')
            ->field('concat(p.province,c.city,u.county,a.addres) as  addres,a.contacts,co.con_time,a.order_id,a.state,go.title,a.assignor')
            ->join('province p', 'a.province_id=p.province_id', 'left')
            ->join('city c', 'a.city_id=c.city_id', 'left')
            ->join('county u', 'a.county_id=u.county_id', 'left')
            ->join('user us', 'a.assignor=us.user_id', 'left')
            ->join('goods_category go', 'a.pro_id=go.id', 'left')
            ->join('contract co', 'a.order_id=co.orders_id', 'left');
        switch ($params['type']) {
            //签约时间
            case 1:
                $m->whereBetween('co.con_time', [$start, $end]);
                $m->order('co.con_time desc');
                break;
            //完工时间
            case 2:
                $m->whereBetween('a.finish_time', [$start, $end]);
                $m->order('a.finish_time desc');
                break;
            //完款时间
            case 3:
                $m->whereBetween('a.received_time', [$start, $end]);
                $m->order('a.received_time desc');
                break;
            //结算时间
            default :
                $m->whereBetween('a.cleared_time', [$start, $end]);
                $m->order('a.cleared_time desc');
                break;
        }
        if ($this->us['reserve'] == 2) {
            $m->where('a.assignor', $this->us['user_id']);
        } else{
            $m->where('us.store_id', $this->us['store_id']);
        }
        $list =$m->where('us.ce', '<>', 2)->select();
      
        $count=0;
        foreach ($list as $k=>$key) {
            $app_orders=[];
            //签约时间
            $list[$k]['con_time']=!empty($key['con_time'])? date('Y-m-d H:i:s', $key['con_time']) : '';
            
            $app_order=Db::connect(config('database.db2'))->table('app_user_order');
            if (isset($params['user_id']) && $params['user_id'] != 0) {
                $app_order->where('user_id', $params['user_id']);
            }
           
            $app_user_order=$app_order->where('order_id', $key['order_id'])->whereNull('deleted_at')->select();
          
            foreach ($app_user_order as $value=>$item) {
                
                $personal_price=Db::connect(config('database.db2'))->table('app_user_order_capital')->join('app_user', 'app_user.id=app_user_order_capital.user_id', 'left')->where('app_user_order_capital.order_id', $item['order_id'])->join(config('database.database').'.capital', 'capital.capital_id=app_user_order_capital.capital_id', 'left')->where(['capital.types'=>1, 'capital.enable'=>1, 'capital.agency'=>0])->where('app_user_order_capital.user_id', $item['user_id'])->whereNull('app_user_order_capital.deleted_at')->field('sum(app_user_order_capital.personal_price) as personal_price,app_user.username')->select();
                if (!empty($personal_price[0]['username'])) {
                    $app_orders[$value]['money']   =sprintf('%.2f', array_sum(array_column($personal_price, 'personal_price')));
                    $app_orders[$value]['username']=!empty($personal_price[0]['username'])? $personal_price[0]['username'] : 0;
                }
            }
            if (empty($app_orders)) {
                unset($list[$k]);
            } else{
                $list[$k]['data']=array_values($app_orders);
                $count           +=array_sum(array_column(array_values($app_orders), 'money'));
            }
            
            
        }
        
        r_date(['data'=>array_values($list), 'count'=>sprintf('%.2f', $count)], 200);
        
    }
    
    /*
     * 储备店长提成
     */
    public function ReserveManager()
    {
        $params    =\request()->get();
        $start     =strtotime(date('Y-m-01 00:00:00', strtotime($params['startTime'])));//获取指定月份的第一天
        $end       =strtotime(date('Y-m-t 23:59:59', strtotime($params['startTime']))); //获取指定月份的最后一天
        $proportion=0;
        if ($this->us['reserve'] == 2 || isset($params['user_id']) && $params['user_id'] != 0) {
            $data=Financial::Commission(0, $params['user_id'], $start, $end)['money'];
            
            if ($data['amounts'] <= 50000) {
                $proportion=0.02;
            } elseif (100000 >= $data['amounts'] && $data['amounts'] >= 50000){
                $proportion=0.03;
            } elseif (200000 >= $data['amounts'] && $data['amounts'] >= 100000){
                $proportion=0.04;
            } elseif ($data['amounts'] >= '200000'){
                $proportion=0.05;
            }
            if ($data['amounts'] + $data['agency'] == 0) {
                $li=0;
            } else{
                $li=sprintf('%.2f', ($data['amounts'] + $data['agency'] - $data['material_usage'] - $data['reimbursement'] - $data['agency_sum'] - $data['money']) / ($data['amounts'] + $data['agency'])) * 100;
            }
            //订单提成
            $TotalCommission=sprintf('%.2f', $data['amounts'] * $proportion);
            if ($TotalCommission < 0) {
                $proportion=0;
            }
            //主材提成
            $AmountOfCommission=sprintf('%.2f', ($data['agency'] - $data['agency_sum']) * 0.1);
            //利润率
            
            $TotalProfit=sprintf('%.2f', $TotalCommission + $AmountOfCommission);
        } else{
            $data              =$this::quan($this->us['store_id'], $start, $end);
            $TotalCommission   =$data['TotalProfit'];
            $li                =$data['li'];
            $TotalProfit       =sprintf('%.2f', $data['TotalProfit'] + $data['AmountOfCommission']);
            $AmountOfCommission=$data['AmountOfCommission'];
            
            if ($data['amounts'] == 0) {
                $proportion=0;
            } else{
                $proportion=$data['TotalProfit'] / $data['amounts'];
            }
            
            
        }
        if(isset($params['kk'])){
            res_date(['datta'=>$data, 'TotalCommission'=>$TotalCommission, 'ti'=>(sprintf('%.2f', $proportion) * 100) . '%', 'ti1'=>'10%', 'proportion'=>$li . '%', 'AmountOfCommission'=>$TotalProfit, 'TotalProfit'=>$AmountOfCommission,], 200);
        }else{
            r_date(['datta'=>$data, 'TotalCommission'=>$TotalCommission, 'ti'=>(sprintf('%.2f', $proportion) * 100) . '%', 'ti1'=>'10%', 'proportion'=>$li . '%', 'AmountOfCommission'=>$TotalProfit, 'TotalProfit'=>$AmountOfCommission,], 200);
        }
      
        
    }
    
    /*
    * 全部提成合计
    */
    public static function quan($store_id, $start, $end)
    {
        
        $quer=\db('user')->where('store_id', $store_id)->where('reserve', 2)->where('ce', '<>', 2)->column('user_id');
        $date=[];
        foreach ($quer as $k=>$value) {
            $data      =Financial::Commission(0, $value, $start, $end)['money'];
            $proportion=0;
            if ($data['amounts'] <= 50000) {
                $proportion=0.02;
            } elseif (100000 >= $data['amounts'] && $data['amounts'] >= 50000){
                $proportion=0.03;
            } elseif (200000 >= $data['amounts'] && $data['amounts'] >= 100000){
                $proportion=0.04;
            } elseif ($data['amounts'] >= '200000'){
                $proportion=0.05;
            }
            
            //订单提成
            $TotalCommission=sprintf('%.2f', $data['amounts'] * $proportion);
            //主材提成
            $AmountOfCommission=sprintf('%.2f', ($data['agency'] - $data['agency_sum']) * 0.1);
            //利润率
            $TotalProfit                =sprintf('%.2f', $TotalCommission + $AmountOfCommission);
            $date[$k]['TotalCommission']=$proportion;
            //材料报销
            $date[$k]['AmountOfCommission']=$AmountOfCommission;
            //报销费用
            $date[$k]['TotalProfit']   =$TotalProfit;
            $date[$k]['amounts']       =$data["amounts"];
            $date[$k]["agency"]        =$data["agency"];
            $date[$k]["material_usage"]=$data["material_usage"];
            $date[$k]["reimbursement"] =$data["reimbursement"];
            $date[$k]["agency_sum"]    =$data["agency_sum"];
            $date[$k]["money"]         =$data["money"];
        }
        $money['TotalProfit']       =sprintf('%.2f', array_sum(array_column($date, 'TotalProfit')));
        $money['TotalCommission']   =sprintf('%.2f', array_sum(array_column($date, 'TotalCommission')));
        $money['AmountOfCommission']=sprintf('%.2f', array_sum(array_column($date, 'AmountOfCommission')));
        $money['amounts']           =sprintf('%.2f', array_sum(array_column($date, 'amounts')));
        $money['agency']            =sprintf('%.2f', array_sum(array_column($date, 'agency')));
        $money['material_usage']    =sprintf('%.2f', array_sum(array_column($date, 'material_usage')));
        $money['reimbursement']     =sprintf('%.2f', array_sum(array_column($date, 'reimbursement')));
        $money['agency_sum']        =sprintf('%.2f', array_sum(array_column($date, 'agency_sum')));
        $money['money']             =sprintf('%.2f', array_sum(array_column($date, 'money')));
        
        if ($money['amounts'] + $money['agency'] == 0) {
            $money['li']=0;
        } else{
            $money['li']=sprintf('%.2f', ($money['amounts'] + $money['agency'] - $money['material_usage'] - $money['reimbursement'] - $money['agency_sum'] - $money['money']) / ($money['amounts'] + $money['agency'])) * 100;
        }
        return $money;
        
        
    }
    
    /*
     * 店长分红
     */
    public function StoreManagerBonus(OrderModel $orderModel)
    {
        $params       =\request()->get();
        $start        =strtotime(date('Y-m-01 00:00:00', strtotime($params['startTime'])));//获取指定月份的第一天
        $end          =strtotime(date('Y-m-t 23:59:59', strtotime($params['startTime']))); //获取指定月份的最后一天
        $main_payment =0;
        $agent_payment=0;
        $io           =db('payment')->join('order', 'order.order_id=payment.orders_id', 'left')->join('user', 'order.assignor=user.user_id', 'left')->where('user.store_id', $this->us['store_id'])->whereBetween('payment.uptime', [$start, $end])->where('payment.cleared_time', '>', 0)->field('payment.money,payment.weixin,payment.success,payment.material,payment.agency_material')->select();
        
        foreach ($io as $value) {
            if ($value['weixin'] != 2) {
                if ($value['agency_material'] == 0 && $value['material'] == 0) {
                    $main_payment+=$value['money'];
                }
                if ($value['material'] != 0) {
                    $main_payment+=$value['material'];
                }
            }
            if ($value['weixin'] == 2 && $value['success'] == 2) {
                if ($value['agency_material'] == 0 && $value['material'] == 0) {
                    $main_payment+=$value['money'];
                }
                if ($value['material'] != 0) {
                    $main_payment+=$value['material'];
                }
            }
        }
        
        $io1=db('payment')->join('order', 'order.order_id=payment.orders_id', 'left')->join('user', 'order.assignor=user.user_id', 'left')->where('user.store_id', $this->us['store_id'])->whereBetween('order.settlement_time', [$start, $end])->field('payment.money,payment.weixin,payment.success,payment.material,payment.agency_material')->select();
        foreach ($io1 as $value) {
            if ($value['weixin'] != 2) {
                if ($value['agency_material'] != 0) {
                    $agent_payment+=$value['agency_material'];
                }
                
            }
            if ($value['weixin'] == 2 && $value['success'] == 2) {
                if ($value['agency_material'] != 0) {
                    $agent_payment+=$value['agency_material'];
                }
            }
        }
        
        
        //人工费用
//        $list          =$orderModel->where('assignor', $this->us['user_id'])->whereBetween('received_time', [$start, $end])->where('state', 7)->distinct(true)->column('order_id');
////        $app_user_order=Db::connect(config('database.db2'))->table('app_user_order')->whereIn('order_id', $list)->whereNull('deleted_at')->column('order_id');
//        $app_orders=Db::connect(config('database.db2'))->table('app_user_order_capital')->join('app_user', 'app_user.id=app_user_order_capital.user_id', 'left')->whereIn('app_user_order_capital.order_id',$list)->whereNull('app_user_order_capital.deleted_at')->sum('app_user_order_capital.personal_price');
        $da['material']       =(sprintf('%.2f', $main_payment));
        $da['agency_material']=(sprintf('%.2f', $agent_payment));
//        $da['money']               =$app_orders;
        $da['material_usage']=sprintf('%.2f', db('material_usage')->join('user', 'material_usage.shopowner_id=user.user_id', 'left')->where('user.store_id', $this->us['store_id'])->where('material_usage.status', 1)->whereBetween('material_usage.adopt', [$start, $end])->sum('total_price'));
        
        $da['reimbursement']=sprintf('%.2f', db('reimbursement')->join('user', 'reimbursement.shopowner_id=user.user_id', 'left')->where('user.store_id', $this->us['store_id'])->whereBetween('reimbursement.adopt', [$start, $end])->where(['reimbursement.status'=>1, 'reimbursement.classification'=>['<>', 4]])->sum('money'));
        
        $da['agency_reimbursement']=sprintf('%.2f', db('reimbursement')->join('user', 'reimbursement.shopowner_id=user.user_id', 'left')->join('order', 'reimbursement.order_id=order.order_id', 'left')->where('user.store_id', $this->us['store_id'])->where('user.ce', 'neq', 2)->whereBetween('order.settlement_time', [$start, $end])->where(['reimbursement.status'=>1, 'reimbursement.classification'=>4])->sum('money'));
        $da['shopExpenses']        =sprintf('%.2f', $this->shopExpenses(2, $params['startTime']));
        $proportion                =0;
//        if ($da['shopExpenses'] > 0) {
//            if ($da['shopExpenses'] && $da['shopExpenses'] <= 50000) {
//                $proportion=0.05;
//            } elseif (100000 >= $da['shopExpenses'] && $da['shopExpenses'] >= 50000){
//                $proportion=0.1;
//            } elseif ($da['shopExpenses'] && $da['shopExpenses'] >= 100000){
//                $proportion=0.2;
//            }
//        }
        $op             =Financial::quan($this->us['store_id'], $start, $end);
        $money          =$op['TotalProfit'];
        $TotalCommission=sprintf('%.2f', ($da['material'] + $da['agency_material'] + $money - $da['shopExpenses'] - $da['material_usage'] - $da['reimbursement']) - $da['agency_reimbursement']);
        
        if ($TotalCommission > 0) {
            if ($TotalCommission && $TotalCommission <= 50000) {
                $proportion=0.05;
            } elseif (100000 >= $TotalCommission && $TotalCommission >= 50000){
                $proportion=0.1;
            } elseif ($TotalCommission && $TotalCommission >= 100000){
                $proportion=0.2;
            }
        }
        
        if ($TotalCommission < 0) {
            $proportion=0;
        }
        $TotalProfit=sprintf('%.2f', $TotalCommission * $proportion);
        if ($da['material'] + $da['agency_material'] == 0) {
            $AmountOfCommission=0;
        } else{
            $AmountOfCommission=(sprintf('%.2f', $TotalCommission / ($da['material'] + $da['agency_material'])) * 100) . '%';
        }
        
        r_date(['datta'=>$da, 'TotalCommission'=>$TotalCommission, 'proportion'=>$AmountOfCommission, 'AmountOfCommission'=>$TotalProfit, 'TotalProfit'=>(sprintf('%.2f', $proportion) * 100) . '%', 'ti1'=>'10%'], 200);
    }
    
    /*
     * 主合同收款金额
     */
    public function CollectionOfMainContract()
    {
        $param=Request::instance()->get();
        $start=strtotime(date('Y-m-01 00:00:00', strtotime($param['startTime'])));//获取指定月份的第一天
        $end  =strtotime(date('Y-m-t 23:59:59', strtotime($param['startTime']))); //获取指定月份的最后一天
        //排除测试店长
        $order_id=$this->testStoreManageOrderId($param);
        
        //收款列表
        $_payment=db('payment');
        $_payment->join('order order', 'order.order_id=payment.orders_id', 'left');
        //$_payment->where('payment.agency_material','=',0);
        $_payment->where('payment.money', '>', 0);
        $_payment->where('payment.cleared_time', '>', 0);
        if (count($order_id['order']) > 0) {
            $_payment->whereNotIn('orders_id', $order_id['order']);
        }
//        $_payment->whereRaw('IF(payment.weixin=2 && payment.success=2,payment.success=1)');
        $l[]=['exp', Db::raw("IF(payment.weixin=2,payment.success=2,payment.success=1)")];
        $_payment->where($l);
        
        $_payment->where('payment.uptime', 'between time', [$start, $end]);//旧算法，以收款时间为记
        //$_payment->where('payment.cleared_time','between time',[$param['begin'],$param['end']]);//新算法，以入账时间为记
        $_payment->whereIn('order.assignor', $order_id['user_id']);
        $_payment->field('order.addres as title,payment.weixin,payment.success,payment.money as mon,payment.material,payment.uptime,payment.cleared_time,order.order_id as id,order.assignor as user_id');
        $data=$_payment->page($param['page'], $param['limit'])->select();
        
        foreach ($data as $key=>$value) {
            //$data['data'][$key]['title']=payment_type($value['weixin']);
            $data[$key]['time']=date('Y-m-d H:i:s', $value['uptime']);
            if ($value['weixin'] == 2 && $value['success'] != 2) {
                $value['mon']=0;
            }
            if ($value['material'] == 0 && $value['mon'] > 0) {
                $data[$key]['money']=$value['mon'];
            } else{
                $data[$key]['money']=$value['material'];
            }
        }
        r_date(['datta'=>array_values($data)], 200);
    }
    
    //代购合同收款金额
    public function storeNodeAgent()
    {
        $param=Request::instance()->get();
        $start=strtotime(date('Y-m-01 00:00:00', strtotime($param['startTime'])));//获取指定月份的第一天
        $end  =strtotime(date('Y-m-t 23:59:59', strtotime($param['startTime']))); //获取指定月份的最后一天
        //排除测试店长
        $order_id=$this->testStoreManageOrderId($param);
        //收款列表
        $_payment=db('payment');
        $_payment->join('order order', 'order.order_id=payment.orders_id', 'left');
        $_payment->where('payment.agency_material', '>', 0);
        $_payment->where('payment.money', '>', 0);
        if (count($order_id['order']) > 0) {
            $_payment->whereNotIn('orders_id', $order_id['order']);
        }
        $l[]=['exp', Db::raw("IF(payment.weixin=2,payment.success=2,payment.success=1)")];
        $_payment->where($l);
//        $_payment->where('payment.uptime', 'between time', [$start, $end]);//旧算法，以收款时间为记
        $_payment->where('order.settlement_time', 'between time', [$start, $end]);//新算法，以入账时间为记
        $_payment->whereIn('order.assignor', $order_id['user_id']);
        $_payment->field('order.addres as title,payment.weixin,payment.success,payment.agency_material,payment.uptime,payment.cleared_time,order.order_id as id,order.assignor as user_id');
        $data=$_payment->page($param['page'], $param['limit'])->select();
        
        foreach ($data as $key=>$value) {
            //$data['data'][$key]['title']=payment_type($value['weixin']);
            $data[$key]['time']=date('Y-m-d H:i:s', $value['uptime']);
            if ($value['weixin'] == 2 && $value['success'] != 2) {
                $value['agency_material']=0;
            }
            $data[$key]['money']=$value['agency_material'];
        }
        r_date(['datta'=>array_values($data)], 200);
    }
    
    //材料费用
    public function storeNodeMaterial()
    {
        $param          =Request::instance()->get();
        $order_id       =$this->testStoreManageOrderId($param);
        $start          =strtotime(date('Y-m-01 00:00:00', strtotime($param['startTime'])));//获取指定月份的第一天
        $end            =strtotime(date('Y-m-t 23:59:59', strtotime($param['startTime']))); //获取指定月份的最后一天
        $_material_usage=db('material_usage')->alias('mu');
        $_material_usage->whereIn('shopowner_id', $order_id['user_id']);
        $_material_usage->where('status', '=', 1);//状态是通过审核
        $_material_usage->where('adopt', 'between time', [$start, $end]);//过审时间范围内的
        $_material_usage->field('mu.material_name as title,mu.adopt,mu.total_price as money,mu.order_id as id,mu.shopowner_id as user_id');
        $data=$_material_usage->page($param['page'], $param['limit'])->select();
        
        //dd($data);
        foreach ($data as $key=>$value) {
            $data[$key]['time']=date('Y-m-d H:i:s', $value['adopt']);
        }
        r_date(['datta'=>array_values($data)], 200);
    }
    
    //报销费用主材报销
    public function storeNodeReserveCost()
    {
        $param=Request::instance()->get();
        $user =$this->testStoreManageOrderId($param);
        $start=strtotime(date('Y-m-01 00:00:00', strtotime($param['startTime'])));//获取指定月份的第一天
        $end  =strtotime(date('Y-m-t 23:59:59', strtotime($param['startTime']))); //获取指定月份的最后一天
        
        $_reimbursement=db('reimbursement');
        
        if ($param['is_material'] == 'true') {
            $_reimbursement->join('order', 'reimbursement.order_id=order.order_id', 'left')
                ->where('order.settlement_time', 'between time', [$start, $end])
                ->where('reimbursement.status', 1)
                ->where('reimbursement.classification', 4);
        } else{
            $_reimbursement->whereBetween('reimbursement.adopt', [$start, $end])->where(['reimbursement.status'=>1])->where('classification', '<>', 4);
        }
        
        $_reimbursement->whereIn('reimbursement.shopowner_id', $user['user_id'])->field('reimbursement.reimbursement_name as title,reimbursement.money,reimbursement.order_id as id,adopt,reimbursement.shopowner_id as user_id');
        $data=$_reimbursement->page($param['page'], $param['limit'])->select();
        
        foreach ($data as $key=>$value) {
            $data[$key]['time']=date('Y-m-d H:i:s', $value['adopt']);
            
        }
        r_date(['datta'=>array_values($data)], 200);
    }
    
    public function testStoreManageOrderId($param)
    {
        $order_id=db('order')->join('user', 'order.assignor=user.user_id', 'left')->where('user.ce', 2)->column('order.order_id');
        if ($param['user_id'] == 0) {
            $store_id=$this->us['store_id'];
        } else{
            $store_id=\db('user')->where('user.ce', 'neq', 2)->where('user_id', $param['user_id'])->value('store_id');
        }
        $user_id=\db('user')->where('store_id', $store_id)->column('user_id');
        
        return ['order'=>$order_id, 'user_id'=>$user_id];
    }
    
    /*
     * 我的业绩
     */
    public function achievement(OrderModel $orderModel)
    {
        
        $params=\request()->get();
        $start =strtotime(date('Y-m-01 00:00:00', strtotime($params['startTime'])));//获取指定月份的第一天
        $end   =strtotime(date('Y-m-t 23:59:59', strtotime($params['startTime']))); //获取指定月份的最后一天
        
        $m=$orderModel->table('order')
            ->alias('a')
            ->field('concat(p.province,c.city,u.county,a.addres) as  addres,a.contacts,co.con_time,a.order_id,a.state,a.assignor,go.title')
            ->join('province p', 'a.province_id=p.province_id', 'left')
            ->join('city c', 'a.city_id=c.city_id', 'left')
            ->join('county u', 'a.county_id=u.county_id', 'left')
            ->join('user us', 'a.assignor=us.user_id', 'left')
            ->join('goods_category go', 'a.pro_id=go.id', 'left')
            ->join('contract co', 'a.order_id=co.orders_id', 'left')
            ->where('a.assignor', $this->us['user_id']);
        
        switch ($params['type']) {
            //签约时间
            case 1:
                $m->whereBetween('co.con_time', [$start, $end]);
                $m->order('co.con_time desc');
//                $list=$m->where('us.ce', '<>', 2)->where('a.state', '<>', 9)->select();
                $list=$m->whereBetween('a.state',[4,7])->select();
                
                break;
            //增项
            case 2:
                $list=$this->zeng($start,$end,$this->us['user_id']);
                break;
            //减项
            case 3:
                $list=$this->jian($start,$end,$this->us['user_id']);
                break;
            //退单
            default :
                $list=$this->tui($start,$end,$this->us['user_id']);
               
                break;
        }
        foreach ($list as $k=>$key) {
            $data                =[];
            $list[$k]['con_time']=!empty($key['con_time'])? date('Y-m-d H:i:s', $key['con_time']) : '';
            $mouth               =$orderModel->offer($key['order_id']);
            //签约金额
            $list[$k]['amount']=sprintf('%.2f', $mouth['amount']);
            //代购主材
            $list[$k]['agency']=sprintf('%.2f', $mouth['agency']);
            $da['ification']   =2;
            $da['order_id']    =$key['order_id'];
            $data[]            =$da;
            //报销金额
            $list[$k]['agency_sum']=sprintf('%.2f', db('reimbursement')->where(['order_id'=>$key['order_id'], 'status'=>1, 'classification'=>['<>', 4]])->sum('money'));
            $capital               =$cap=db('capital')->where(['ordesr_id'=>$key['order_id'], 'types'=>1, 'enable'=>1])->field('class_b,capital_id')->column('capital_id');
            if (!empty($capital)) {
                $sql="SELECT SUM(`personal_price`) AS sum FROM app_user_order_capital AS a, (SELECT b.`user_id`, b.`order_id`,b.`capital_id`,MAX(b.created_at) AS `created_at` FROM app_user_order_capital AS b GROUP BY b.`user_id`,b.`order_id`,b.`capital_id`)AS c WHERE a.`user_id`=c.`user_id` AND a.`order_id`=c.`order_id` AND a.`capital_id`=c.`capital_id` AND a.created_at = c.created_at And a.`deleted_at` IS NULL AND a.`capital_id` in(" . implode(',', $capital) . ")";
                //原生sql
                $reality_artificial=Db::connect(config('database.db2'))->table('app_user_order_capital');
                $sum               =$reality_artificial->query($sql);
                if (isset($sum[0]['sum'])) {
                    $order_for_reality_artificial=round($sum[0]['sum'], 2);
                } else{
                    $order_for_reality_artificial=0;
                }
            } else{
                $order_for_reality_artificial=0;
            }
            //人工实际
            $list[$k]['estimate']=sprintf('%.2f', $order_for_reality_artificial);
            //材料实际
            $list[$k]['MaterialBudget']=sprintf('%.2f', db('material_usage')
                ->whereIn('order_id', $key['order_id'])
                ->where('status', 1)
                ->sum('total_price'));
            
            //利润
            $list[$k]['profit']=sprintf('%.2f', $list[$k]['amount'] - $list[$k]['estimate'] - $list[$k]['MaterialBudget'] - $list[$k]['agency'] - $list[$k]['agency_sum']);
           
            
        }
        $amount=sprintf('%.2f',array_sum(array_column($list,'amount')));
        $zeng=array_sum(array_column($this->zeng($start,$end,$this->us['user_id']),'turnover'));
        $jian=array_sum(array_column($this->jian($start,$end,$this->us['user_id']),'turnover'));
        $tui=array_sum(array_column($this->tui($start,$end,$this->us['user_id']),'turnover'));
        $ye=sprintf('%.2f',$amount+$zeng-$jian-$tui);
       
        r_date(['zeng'=>$zeng,'jian'=>$jian,'tui'=>$tui,'amount'=>$amount,'ye'=>$ye,'data'=>$list], 200);
        
        
    }
    
    public function zeng($start,$end,$user)
    {
        //查增项
        $increment=db('capital')
            ->join('order order', 'capital.ordesr_id = order.order_id', 'left')
            ->join('contract contract', 'contract.orders_id = capital.ordesr_id', 'left')
            ->join('province p', 'order.province_id=p.province_id', 'left')
            ->join('city c', 'order.city_id=c.city_id', 'left')
            ->join('county u', 'order.county_id=u.county_id', 'left')
            ->join('goods_category go', 'order.pro_id=go.id', 'left')
            ->join('user user', 'user.user_id=order.assignor', 'left')
            ->where('capital.increment', 1)//是增项
            ->where('capital.types', 1)//是增项
            ->where('capital.enable', 1)//是增项
            ->where('capital.ordesr_id', '>', 1)//是增项
            ->where('order.state', '>', 3)
            ->where('order.state', '<', 8)//成交
            ->where('contract.con_time', '<', $start)//在签约时间以前的增项
            ->where('capital.crtime', 'between time', [$start, $end])//在时间范围内的增项
            ->where('order.assignor', $user)//用户相关的
            ->field('order.order_id,user.user_id, user.store_id, SUM(to_price) as turnover,capital.crtime as times,contract.con_time,concat(p.province,c.city,u.county,order.addres) as  addres,order.contacts,go.title')
            ->group('capital.ordesr_id')
            ->select();
    
        return $increment;
        
    }
    
    public function jian($start,$end,$user)
    {
        //查减项
        $deduction=db('capital')
            ->join('order order', 'capital.ordesr_id = order.order_id', 'left')
            ->join('contract contract', 'contract.orders_id = capital.ordesr_id', 'left')
            ->join('user user', 'user.user_id=order.assignor', 'left')
            ->join('province p', 'order.province_id=p.province_id', 'left')
            ->join('city c', 'order.city_id=c.city_id', 'left')
            ->join('county u', 'order.county_id=u.county_id', 'left')
            ->join('goods_category go', 'order.pro_id=go.id', 'left')
            ->where('capital.untime', '>', 0)//是减项
            ->where('order.state', '>', 3)
            ->where('order.state', '<', 8)//成交
            ->where('contract.con_time', '<', $start)//在签约时间以前的增项
            ->where('capital.untime', 'between time', [$start, $end])//减项在时间范围内
            ->where('order.assignor', $user)//用户相关的
            ->field('order.order_id,user.user_id,user.store_id,SUM(to_price) as turnover,capital.untime as times,contract.con_time,concat(p.province,c.city,u.county,order.addres) as  addres,order.contacts,go.title')
            ->group('capital.ordesr_id')
            ->select();
        return $deduction;
    }
    public function tui($start,$end,$user)
    {
        //查退单
        $deduction=db('capital')
            ->join('order order', 'capital.ordesr_id = order.order_id', 'left')
            ->join('province p', 'order.province_id=p.province_id', 'left')
            ->join('city c', 'order.city_id=c.city_id', 'left')
            ->join('county u', 'order.county_id=u.county_id', 'left')
            ->join('goods_category go', 'order.pro_id=go.id', 'left')
            ->join('contract contract', 'contract.orders_id = capital.ordesr_id', 'left')
            ->join('user user', 'user.user_id=order.assignor', 'left')
            ->join('envelopes envelopes', 'envelopes.envelopes_id=capital.envelopes_id', 'left')
            ->where('contract.con_time', '<', $start)//在签约时间以前的增项
            ->whereBetween('order.tui_time', [$start,$end])//在签约时间以前的增项
            ->where('order.state', 9)//在签约时间以前的增项
            ->where('order.assignor',$user)//用户相关的
            ->where('capital.types',1)//用户相关的
            ->where('capital.enable',1)//用户相关的
            ->field('order.order_id,user.user_id,user.store_id,(SUM(to_price)+envelopes.expense+envelopes.purchasing_expense-envelopes.give_money -envelopes.purchasing_discount )as turnover,capital.untime as times,contract.con_time,concat(p.province,c.city,u.county,order.addres) as  addres,order.contacts,go.title')
            ->group('capital.ordesr_id')
            ->select();
        return $deduction;
    }
    
    public function TurnoverRate($type='')
    {
      
        $params=\request()->get();
        if(!isset($params['startTime'])){
            $startTime=time();
        }else{
            $startTime=strtotime($params['startTime']);
        }
        $start =strtotime(date('Y-m-01 00:00:00', $startTime));//获取指定月份的第一天
        $end   =strtotime(date('Y-m-t 23:59:59', $startTime)); //获取指定月份的最后一天
        //成交量
        $g1       =db('contract')->join('order', 'order.order_id=contract.orders_id', 'left')->whereBetween('order.state', [4, 7])->whereBetween('contract.con_time', [$start, $end])->where('contract.type', 1)->where('order.assignor', $this->us['user_id'])->field('contract.orders_id,order.created_time,order.channel_details')->select();
        $order_ids=[];
        $order=[];
        foreach ($g1 as $key=>$val) {
            $order_ids[]=$val['orders_id'];
            if($val['created_time']<$start){
                $order[]=$val['orders_id'];
            }
            
        }
        
        $date['cheng']=count($g1);
        
        //接单量
        $date['jie']=db('order')->where(['assignor'=>$this->us['user_id'], 'created_time'=>['between', [$start, $end]]])->count();
        //无效订单
        $date['wu']=db('order')->where(['assignor'=>$this->us['user_id'], 'created_time'=>['between', [$start, $end]]])->where('state', 10)->count();
        //退单
        $date['tui']=db('order')->where(['assignor'=>$this->us['user_id'], 'created_time'=>['between', [$start, $end]]])->whereBetween('tui_time',[$start, $end])->where('state', 9)->count();
        //活动订单
        $date['huo']=db('order')->where(['assignor'=>$this->us['user_id'], 'created_time'=>['between', [$start, $end]]])->where('state', 'neq', 10)->where('channel_details', 96)->count();
        //历史活动订单
        $date['history']=count($order);
        //成交的活动订单
        if (!empty($order_ids)) {
            $date['cheng_huo']=db('order')->whereIn('order_id', $order_ids)->where('channel_details', 96)->count();
        }else{
            $date['cheng_huo']=0;
        }
       
        $shang_count=$date['jie'] - $date['huo']-$date['wu'] + $date['cheng_huo']+$date['history'];
        //上门量订单包括取消的
        if (!empty($shang_count)) {
            $date['turnovers']=sprintf('%.2f',((count($g1)-$date['tui']) / $shang_count) * 100).'%';//上门成交率
        } else{
            if (empty($shang_count)) {
                $date['turnovers']=100;
            } else{
                $date['turnovers']=0;
            }
            
        }
        if($type=='ss'){
            return $date['turnovers'];
        }
        r_date($date, 200);
    }
}